package com.ruoyi.system.utils;

import com.ruoyi.system.domain.BydspecialPoject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelImporterUtils {
    private static final Map<String, String> HEADER_MAPPING = new HashMap<>();
    static {
        HEADER_MAPPING.put("部门", "department");
        HEADER_MAPPING.put("业务员", "salesman");
        HEADER_MAPPING.put("客户名称", "customerName");
        HEADER_MAPPING.put("客户订单号", "customerOrder");
        HEADER_MAPPING.put("类别", "category");
        HEADER_MAPPING.put("物料代码", "materialCode");
        HEADER_MAPPING.put("产品名称", "productName");
        HEADER_MAPPING.put("产品型号", "productModel");
        HEADER_MAPPING.put("合同备注", "contractRemarks");
        HEADER_MAPPING.put("单位", "unit");
        HEADER_MAPPING.put("订单数量", "orderQuantity");
        HEADER_MAPPING.put("下单日期", "orderDate");
        HEADER_MAPPING.put("合同交期", "deliveryDate");
        HEADER_MAPPING.put("变更后交期", "changeDate");
        HEADER_MAPPING.put("责任工程", "responsibleEngineer");
        HEADER_MAPPING.put("生产状态", "productionStatus");
        HEADER_MAPPING.put("入库时间", "storageTime");
        HEADER_MAPPING.put("出货日期", "shippingDate");
        HEADER_MAPPING.put("已交数量", "quantityShipped");
        // 可以根据需要添加更多映射
    }
    public static List<BydspecialPoject> parseExcel(MultipartFile file) throws IOException {
        List<BydspecialPoject> userList = new ArrayList<>();
        Map<String, Integer> headerIndexMap = new HashMap<>();

        try (InputStream is = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(is)) {

            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
            Iterator<Row> rowIterator = sheet.iterator();

            // 读取表头
            if (rowIterator.hasNext()) {
                Row headerRow = rowIterator.next();
                Iterator<Cell> cellIterator = headerRow.cellIterator();

                int index = 0;
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    String headerName = cell.getStringCellValue().trim();
                    if (HEADER_MAPPING.containsKey(headerName)) {
                        headerIndexMap.put(HEADER_MAPPING.get(headerName), index);
                    }
                    index++;
                }
            }

            // 读取数据行
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (isRowEmpty(row)) {
                    continue; // 跳过空行
                }

                BydspecialPoject bydspecialPoject = new BydspecialPoject();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

                // 根据表头映射填充数据
                if (headerIndexMap.containsKey("department")) {
                    bydspecialPoject.setDepartment(getCellValueAsString(row.getCell(headerIndexMap.get("department"))));
                }

                if (headerIndexMap.containsKey("salesman")) {
                    bydspecialPoject.setSalesman(getCellValueAsString(row.getCell(headerIndexMap.get("salesman"))));

                }
                if (headerIndexMap.containsKey("customerName")) {
                    bydspecialPoject.setCustomerName(getCellValueAsString(row.getCell(headerIndexMap.get("customerName"))));
                }
                if (headerIndexMap.containsKey("customerOrder")) {
                    bydspecialPoject.setCustomerOrder(getCellValueAsString(row.getCell(headerIndexMap.get("customerOrder"))));
                }
                if (headerIndexMap.containsKey("category")) {
                    bydspecialPoject.setCategory(getCellValueAsString(row.getCell(headerIndexMap.get("category"))));
                }
                if (headerIndexMap.containsKey("materialCode")) {
                    bydspecialPoject.setMaterialCode(getCellValueAsString(row.getCell(headerIndexMap.get("materialCode"))));
                }
                if (headerIndexMap.containsKey("productName")) {
                    bydspecialPoject.setProductName(getCellValueAsString(row.getCell(headerIndexMap.get("productName"))));
                }
                if (headerIndexMap.containsKey("productModel")) {
                    bydspecialPoject.setProductModel(getCellValueAsString(row.getCell(headerIndexMap.get("productModel"))));
                }
                if (headerIndexMap.containsKey("contractRemarks")) {
                    bydspecialPoject.setContractRemarks(getCellValueAsString(row.getCell(headerIndexMap.get("contractRemarks"))));
                }
                if (headerIndexMap.containsKey("unit")) {
                    bydspecialPoject.setUnit(getCellValueAsString(row.getCell(headerIndexMap.get("unit"))));
                }
                if (headerIndexMap.containsKey("orderQuantity")) {
                    bydspecialPoject.setOrderQuantity(getCellValueAsString(row.getCell(headerIndexMap.get("orderQuantity"))));
                }
                if (headerIndexMap.containsKey("orderDate")) {
                    String date = getCellValueAsString(row.getCell(headerIndexMap.get("orderDate")));
                    if (!date.isEmpty() || date != ""){
                        bydspecialPoject.setOrderDate(sdf.parse(getCellValueAsString(row.getCell(headerIndexMap.get("orderDate")))));
                    }
                }
                if (headerIndexMap.containsKey("deliveryDate")) {
                    String date = getCellValueAsString(row.getCell(headerIndexMap.get("deliveryDate")));
                    if (!date.isEmpty() || date != ""){
                        bydspecialPoject.setDeliveryDate(sdf.parse(getCellValueAsString(row.getCell(headerIndexMap.get("deliveryDate")))));
                    }
                }
                if (headerIndexMap.containsKey("changeDate")) {
                    String date = getCellValueAsString(row.getCell(headerIndexMap.get("changeDate")));
                    if (!date.isEmpty() || date != ""){
                        bydspecialPoject.setChangeDate(sdf.parse(getCellValueAsString(row.getCell(headerIndexMap.get("changeDate")))));
                    }
                }
                if (headerIndexMap.containsKey("responsibleEngineer")) {
                    bydspecialPoject.setResponsibleEngineer(getCellValueAsString(row.getCell(headerIndexMap.get("responsibleEngineer"))));
                }
                if (headerIndexMap.containsKey("productionStatus")) {
                    bydspecialPoject.setProductionStatus(getCellValueAsString(row.getCell(headerIndexMap.get("productionStatus"))));
                }
                if (headerIndexMap.containsKey("storageTime")) {
                    String date = getCellValueAsString(row.getCell(headerIndexMap.get("storageTime")));
                    if (!date.isEmpty() || date != ""){
                        bydspecialPoject.setStorageTime(sdf.parse(getCellValueAsString(row.getCell(headerIndexMap.get("storageTime")))));
                    }

                }
                if (headerIndexMap.containsKey("shippingDate")) {
                    String date = getCellValueAsString(row.getCell(headerIndexMap.get("shippingDate")));
                    if (!date.isEmpty() || date != ""){
                        bydspecialPoject.setShippingDate(sdf.parse(getCellValueAsString(row.getCell(headerIndexMap.get("shippingDate")))));
                    }

                }
                if (headerIndexMap.containsKey("quantityShipped")) {
                    bydspecialPoject.setQuantityShipped(getCellValueAsString(row.getCell(headerIndexMap.get("quantityShipped"))));
                }

                userList.add(bydspecialPoject);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return userList;
    }
    private static boolean isRowEmpty(Row row) {
        if (row == null) {
            return true;
        }

        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }

        return true;
    }
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 日期格式处理
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(date);
                } else {
                    // 数字格式处理
                    double numericValue = cell.getNumericCellValue();
                    // 如果是整数，去掉小数点和小数部分
                    if (numericValue == Math.floor(numericValue)) {
                        return String.valueOf((int) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                // 计算公式结果而不是返回公式本身
                Workbook workbook = cell.getSheet().getWorkbook();
                FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                CellValue cellValue = evaluator.evaluate(cell);
                return getCellValueAsString(cellValue.getCellType(), cellValue);
            default:
                return "";
        }
    }
    private static String getCellValueAsString(CellType cellType, CellValue cellValue) {
        switch (cellType) {
            case STRING:
                return cellValue.getStringValue();
            case NUMERIC:
                double numericValue = cellValue.getNumberValue();
                if (DateUtil.isValidExcelDate(numericValue)) {
                    Date date = DateUtil.getJavaDate(numericValue);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(date);
                } else if (numericValue == Math.floor(numericValue)) {
                    return String.valueOf((int) numericValue);
                } else {
                    return String.valueOf(numericValue);
                }
            case BOOLEAN:
                return String.valueOf(cellValue.getBooleanValue());
            default:
                return "";
        }
}
}